package com.anolesoft.amljmes.delivery.mapper;

import java.util.List;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.amljmes.delivery.dto.MesPtmlExportDto;
import com.anolesoft.amljmes.delivery.dto.MesPtmlIerpExportDto;
import org.apache.ibatis.annotations.*;

import com.anolesoft.amljmes.delivery.dto.MesPtmlBzzDto;
import org.apache.ibatis.jdbc.SQL;

/**
 * 
 * @author 宋佳明
 * @date 2022/12/12
 *
 */
@Mapper
public interface  MesPtmlBzzMapper {

	 //条件查询
//	 List<MesPtmlBzzDto> queryBzzPtml(MesPtmlBzzDto mesBzzPtmlDto);
	 
	 //维护(修改)
	 int updateBzzPtml(MesPtmlBzzDto mesBzzPtmlDto);
	 
	 //导入
	 int importBzzPtml(MesPtmlBzzDto mesBzzPtmlDto);

	/**
	 * 查询两张表显示数据
	 */
	@SelectProvider(type= MesPtmlBzzMapper.createSQL.class, method = "query")
	List<MesPtmlBzzDto> queryBzzPtml(MesPtmlBzzDto mesBzzPtmlDto);
	public static class createSQL {
		public static String query(MesPtmlBzzDto mesBzzPtmlDto){
			return new SQL(){
				{
					StringBuilder sql = new StringBuilder();
					sql.append(" p.objuid,p.xuhao,p.fenxuhao,p.peitaomulubianhao,p.jixing,p.tuhao,p.lingjianmingcheng,p.futuhao," +
							"p.fulingjianmingcheng,p.leibie,p.mululeibie,p.zhuanye,p.peitaoshuliang,p.danwei,p.bili,p.chengzhidanweibianma," +
							"p.chengzhidanwei,p.yijiaodanwei,p.fengongluxian,p.gualiangzhuangqianshiyan,p.shiyongxingjiancha," +
							"p.jiguanwenjian,p.zhuanjizhuanpei,p.jiacikaishi,p.jiacijieshu,p.beizhu,p.wuliaohao,p.wuliaomingcheng," +
							"p.guige,p.paihao,p.jishuzhuangtai,p.jiliangdanwei,p.laiyuanleibie,p.gongyizhuangtai,p.guanjianjianbiaoji," +
							"p.paigonghao,p.jiaofuchejian,p.jieshouchejian,p.jiaofushunxu,w.fengcunfangfa,w.fengcunleixing,w.neibufengong," +
							"w.shifouzuofanghu "  +
							" from  mes_bzz_ptml p left join mes_bzz_ptml_ins w on p.wuliaohao = w.wuliaohao " +
							" where p.shanchubiaoji = 1 ") ;
					if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getJixing())){
						String jixing = mesBzzPtmlDto.getJixing();
						sql.append(" and p.jixing = #{jixing,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getTuhao())){
						String tuhao = mesBzzPtmlDto.getTuhao();
						sql.append(" and p.tuhao like concat(concat('%',#{tuhao,jdbcType=VARCHAR}),'%') ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getWuliaohao())){
						String wuliaohao = mesBzzPtmlDto.getWuliaohao();
						sql.append(" and p.wuliaohao=#{wuliaohao,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getFutuhao())){
						String futuhao = mesBzzPtmlDto.getFutuhao();
						sql.append(" and p.futuhao=#{futuhao,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getZhuanye())){
						String zhuanye = mesBzzPtmlDto.getZhuanye();
						sql.append(" and p.zhuanye=#{zhuanye,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getChengzhidanwei())){
						String chengzhidanwei = mesBzzPtmlDto.getChengzhidanwei();
						sql.append(" and p.chengzhidanwei=#{chengzhidanwei,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getYijiaodanwei())){
						String yijiaodanwei = mesBzzPtmlDto.getYijiaodanwei();
						sql.append(" and p.yijiaodanwei=#{yijiaodanwei,jdbcType=VARCHAR} ");
					}
					sql.append(" order by p.xuhao ");
					SELECT(sql.toString());
				}
			}.toString();
		}
	}
	/**
	 * 维护的时候,根据物料号查询维护表,有数据修改,无数据新增
	 */
	@Select("select count(objuid) from mes_bzz_ptml_ins where wuliaohao = #{wuliaohao}")
	int countweihu(String wuliaohao);

	/**
	 * 新增数据维护表一条数据
	 */
	@Insert("insert into MES_BZZ_PTML_INS(objuid,fengcunleixing,neibufengong,shifouzuofanghu,changdaihao,xiugairen,xiugaishijian,wuliaohao,fengcunfangfa)" +
			" values(sys_guid(),#{fengcunleixing, jdbcType=NUMERIC},#{neibufengong,jdbcType=VARCHAR},#{shifouzuofanghu, jdbcType=NUMERIC}," +
			"#{changdaihao,jdbcType=VARCHAR},#{xiugairen,jdbcType=VARCHAR},sysdate,#{wuliaohao,jdbcType=VARCHAR},#{fengcunfangfa,jdbcType=VARCHAR})")
	int insertweihu(MesPtmlBzzDto mesBzzPtmlDto);

	/**
	 * 根据机型,父图号,子图号,交付车间,接收车间,交付顺序,查询配套目录表,有数据的修改,无的新增
	 */
	@Select("select count(objuid) from MES_BZZ_PTML t where t.jixing = #{jixing} and t.futuhao = #{futuhao} and" +
			" t.wuliaohao = #{wuliaohao} and t.jiaofuchejian = #{jiaofuchejian} and t.jieshouchejian = #{jieshouchejian}" +
			" and t.jiaofushunxu = #{jiaofushunxu}")
	int countPtml(MesPtmlBzzDto mesPtmlBzzDto);

	/**
	 * 配套目录表新增一条数据
	 */
	@Insert("insert into mes_bzz_ptml(jixing,jiacikaishi,jiacijieshu,futuhao,fulingjianmingcheng,wuliaohao," +
			"wuliaomingcheng,leibie,peitaoshuliang,danwei,bili,jiaofuchejian,jieshouchejian,jiaofushunxu,chengzhidanwei,tuhao," +
			"lingjianmingcheng,danjishu) " +
			"values(#{jixing},#{jiacikaishi},#{jiacijieshu},#{futuhao},#{fulingjianmingcheng},#{wuliaohao},#{wuliaomingcheng}," +
			"#{leibie},#{peitaoshuliang},#{danwei},#{bili},#{jiaofuchejian},#{jieshouchejian},#{jiaofushunxu},#{chengzhidanwei}," +
			"#{wuliaohao},#{wuliaomingcheng},#{peitaoshuliang})")
	int insertPtml(MesPtmlBzzDto mesPtmlBzzDto);
	/**
	 * 配套目录修改一条数据
	 */
	@Update("update mes_bzz_ptml set jiacikaishi = #{jiacikaishi}, jiacijieshu = #{jiacijieshu}, fulingjianmingcheng = #{fulingjianmingcheng}, " +
			"wuliaomingcheng = #{wuliaomingcheng}, leibie = #{leibie}, peitaoshuliang = #{peitaoshuliang}, danwei = #{danwei}, " +
			"bili = #{bili}, lingjianmingcheng = #{wuliaomingcheng}, danjishu = #{peitaoshuliang}, chengzhidanwei = #{chengzhidanwei}" +
			"where jixing = #{jixing} and futuhao = #{futuhao} and " +
			"wuliaohao = #{wuliaohao} and jiaofuchejian = #{jiaofuchejian} and jieshouchejian = #{jieshouchejian} " +
			"and jiaofushunxu = #{jiaofushunxu}")
	int updatePtmlzhu(MesPtmlBzzDto mesPtmlBzzDto);

	/**
	 * 导出查询两张表显示数据
	 */
	@SelectProvider(type= MesPtmlBzzMapper.createSQL1.class, method = "query1")
	List<MesPtmlExportDto> queryExport(MesPtmlBzzDto mesBzzPtmlDto);
	public static class createSQL1 {
		public static String query1(MesPtmlBzzDto mesBzzPtmlDto){
			return new SQL(){
				{
					StringBuilder sql = new StringBuilder();
					sql.append(" p.jixing,p.jiacikaishi,p.jiacijieshu,p.futuhao,p.fulingjianmingcheng,p.wuliaohao,p.wuliaomingcheng," +
							"p.leibie,p.peitaoshuliang,p.danwei,p.bili,p.jiaofuchejian,p.jieshouchejian,p.jiaofushunxu,p.chengzhidanwei," +
							"w.fengcunfangfa," +
							"(case w.fengcunleixing when 0 then '内部' else '外部' end) fengcunleixing," +
							"w.neibufengong," +
							"(case w.shifouzuofanghu when 0 then '否' else '是' end) shifouzuofanghu" +
							" from  mes_bzz_ptml p left join mes_bzz_ptml_ins w on p.wuliaohao = w.wuliaohao " +
							" where p.shanchubiaoji = 1 ") ;
					if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getJixing())){
						String jixing = mesBzzPtmlDto.getJixing();
						sql.append(" and p.jixing = #{jixing,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getTuhao())){
					String tuhao = mesBzzPtmlDto.getTuhao();
					sql.append(" and p.tuhao like concat(concat('%',#{tuhao,jdbcType=VARCHAR}),'%') ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getWuliaohao())){
					String wuliaohao = mesBzzPtmlDto.getWuliaohao();
					sql.append(" and p.wuliaohao=#{wuliaohao,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getZhuanye())){
					String zhuanye = mesBzzPtmlDto.getZhuanye();
					sql.append(" and p.zhuanye=#{zhuanye,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getChengzhidanwei())){
					String chengzhidanwei = mesBzzPtmlDto.getChengzhidanwei();
					sql.append(" and p.chengzhidanwei=#{chengzhidanwei,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getYijiaodanwei())){
					String yijiaodanwei = mesBzzPtmlDto.getYijiaodanwei();
					sql.append(" and p.yijiaodanwei=#{yijiaodanwei,jdbcType=VARCHAR} ");
				}
					sql.append(" order by p.xuhao ");
					SELECT(sql.toString());
				}
			}.toString();
		}
	}

	/**
	 * 导出查询两张表显示数据(ierp集成使用)
	 */
	@SelectProvider(type= MesPtmlBzzMapper.createSQL2.class, method = "query2")
	List<MesPtmlIerpExportDto> queryExportIerp(MesPtmlBzzDto mesBzzPtmlDto);
	public static class createSQL2 {
		public static String query2(MesPtmlBzzDto mesBzzPtmlDto){
			return new SQL(){
				{
					StringBuilder sql = new StringBuilder();
					sql.append(" p.wuliaohao," +
							"w.fengcunfangfa," +
							"(case w.fengcunleixing when 0 then '内部' else '外部' end) fengcunleixing," +
							"w.neibufengong," +
							"(case w.shifouzuofanghu when 0 then '否' else '是' end) shifouzuofanghu" +
							" from  mes_bzz_ptml p left join mes_bzz_ptml_ins w on p.wuliaohao = w.wuliaohao " +
							" where p.shanchubiaoji = 1 ") ;
					if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getJixing())){
						String jixing = mesBzzPtmlDto.getJixing();
						sql.append(" and p.jixing = #{jixing,jdbcType=VARCHAR} ");
					}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getTuhao())){
					String tuhao = mesBzzPtmlDto.getTuhao();
					sql.append(" and p.tuhao like concat(concat('%',#{tuhao,jdbcType=VARCHAR}),'%') ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getWuliaohao())){
					String wuliaohao = mesBzzPtmlDto.getWuliaohao();
					sql.append(" and p.wuliaohao=#{wuliaohao,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getZhuanye())){
					String zhuanye = mesBzzPtmlDto.getZhuanye();
					sql.append(" and p.zhuanye=#{zhuanye,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getChengzhidanwei())){
					String chengzhidanwei = mesBzzPtmlDto.getChengzhidanwei();
					sql.append(" and p.chengzhidanwei=#{chengzhidanwei,jdbcType=VARCHAR} ");
				}if(!StrUtil.isEmptyOrUndefined(mesBzzPtmlDto.getYijiaodanwei())){
					String yijiaodanwei = mesBzzPtmlDto.getYijiaodanwei();
					sql.append(" and p.yijiaodanwei=#{yijiaodanwei,jdbcType=VARCHAR} ");
				}
					sql.append(" order by p.xuhao ");
					SELECT(sql.toString());
				}
			}.toString();
		}
	}

}
